<%@ Page language="vb" CodeFile="addInvoiceCredit.aspx.vb" Inherits="addInvoiceCredit_aspx_vb" %>

<%
    rowNumber = 1
    vehicleID = Request.QueryString("vehicleID")
    If Len(vehicleID) > 0 Then 
        vehicleID = CLng(vehicleID)
    End IF
    If (Request.QueryString("do")) = Nothing Then
        subDo = Request.QueryString("subDo")
        dbOpen()
        RecSet = Conn.Execute("Select v.leaseStartDate, c.countryCurrency, c.country, p.partnerName, cl.client, pr.product, pl.priceListDuration, pl.priceListCategory, pl.price, pl.priceListDistance, (Select Count(ic.vehicleID) From (tblInvoiceContent ic INNER JOIN tblInvoice i ON i.invoiceID = ic.invoiceID) Where ic.vehicleID = v.vehicleID AND i.invoiceAmount > 0) As vehicleCount From (((((tblVehicle v INNER JOIN tblCountry c ON c.countryID = v.countryID) INNER JOIN tblPartner p ON p.partnerID = v.partnerID) INNER JOIN tblClient cl ON cl.clientID = v.clientID) INNER JOIN tblProduct pr ON pr.productID = v.productID) INNER JOIN tblPriceList pl ON pl.priceListID = v.priceListID) Where v.vehicleID = " & vehicleID & "")
        If RecSet.EOF Then
            Response.Redirect("searchVehicle.aspx")
        Else
            leaseStartDate = RecSet.Fields("leaseStartDate").Value
            countryCurrency = RecSet.Fields("countryCurrency").Value
            country = RecSet.Fields("country").Value
            partnerName = RecSet.Fields("partnerName").Value
            client = RecSet.Fields("client").Value
            product = RecSet.Fields("product").Value
            priceListDuration = RecSet.Fields("priceListDuration").Value
            priceListCategory = RecSet.Fields("priceListCategory").Value
            price = RecSet.Fields("price").Value
            priceListDistance = RecSet.Fields("priceListDistance").Value
            vehicleCount = RecSet.Fields("vehicleCount").Value
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        dbOpen()
        RecSet = Conn.Execute("Select Sum(ap.price) As additionalPrice From (tblAdditionalProductVehicleContent apc INNER JOIN tblAdditionalProduct ap ON ap.additionalProductID = apc.additionalProductID) Where apc.vehicleID = " & vehicleID & "")
        If Not RecSet.EOF Then
            additionalPrice = RecSet.Fields("additionalPrice").Value
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        If IsDBNull(additionalPrice) = True Then
            additionalPrice = 0
        End If
        price = price + additionalPrice

        leaseEndDate = DateAdd("m", CDbl(priceListDuration), Convert.ToDateTime(leaseStartDate))
        leaseEndDate = DateAdd("m", CDbl("1"), Convert.ToDateTime(leaseEndDate))
        leaseEndDate = Left(leaseEndDate, 8) & "01"
        leaseEndDate = DateAdd("d", CDbl("-1"), Convert.ToDateTime(leaseEndDate))
        Over()
        strJS = ""
        strJS = strJS + "[""leaseEndKms"", ""==''""],"
        strJS = strJS + "[""leaseEndDateYear"", ""<1""],"
        strJS = strJS + "[""leaseEndDateMonth"", ""<1""],"
        strJS = strJS + "[""leaseEndDateDay"", ""<1""],"
        strJS = strJS + "[""actionID"", ""<1""]"
        checkFields()
        realTime = Now()
        realTime = DateAdd("m", CDbl("1"), Convert.ToDateTime(realTime))
        realTime = Left(realTime, 8) & "01"
        realTime = DateAdd("d", CDbl("-1"), Convert.ToDateTime(realTime))
        year = Left(realTime, 4)
        month = Mid(realTime, 6, 2)
        day = Mid(realTime, 9, 2)
        Response.Write("<table cellpadding='0' cellspacing='0' border='0' width='1000'>")
        Response.Write("<tr>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        Response.Write("<tr><td class='headline' colspan='3'>DEACTIVASION<font color='#fcbc00'>" & subDo & "</font><hr class='hrHeadline'></td></tr>")
        Response.Write("<form action='addInvoiceCredit.aspx?do=action&vehicleID=" & vehicleID & "' method='post' onsubmit=""javascript:return checkFields();"">")
        Response.Write("<tr><td class='formBold'>Vehicle kms:</td><td align='right'><input type='text' name='leaseEndKms' id='leaseEndKms' value='' size='40' maxlength='50' onkeyup='re=/(\d*)/; re.exec(this.value); this.value=RegExp.$1;'></td><td><div id='error_leaseEndKms' class='edit_error'>&laquo; OBS!</div></td></tr>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Lease contract end date:&nbsp; </td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='leaseEndDateYear' id='leaseEndDateYear'>")
        Response.Write("<option value=''>&nbsp; Year ")
        year = CLng(year)
        stopYear = year + 3
        startYear = CDbl(year) - 1
        yearCounter = startYear
        For yearLoop = startYear To stopYear
            Response.Write("<option value='" & yearCounter & "'")
            If yearCounter = CDbl(year) Then
                Response.Write("selected='selected'")
            End If
            Response.Write("> " & yearCounter & " ")
            yearCounter = yearCounter + 1
        Next
        Response.Write("</select>")
        Response.Write(" - ")
        Response.Write("<select name='leaseEndDateMonth' id='leaseEndDateMonth'>")
        Response.Write("<option value=''>&nbsp; Month ")
        yearCounter = 1
        For yearLoop = 1 To 12
            Response.Write("<option value='" & yearCounter & "'")
            If yearCounter = CLng(month) Then
                Response.Write("selected='selected'")
            End If
            Response.Write("> " & yearCounter & " ")
            yearCounter = yearCounter + 1
        Next
        Response.Write("</select>")
        Response.Write(" - ")
        Response.Write("<select name='leaseEndDateDay' id='leaseEndDateDay'>")
        Response.Write("<option value=''>&nbsp; Day&nbsp; ")
        yearCounter = 1
        For yearLoop = 1 To 31
            Response.Write("<option value='" & yearCounter & "'")
            If yearCounter = CLng(day) Then
                Response.Write("selected='selected'")
            End If
            Response.Write("> " & yearCounter & " ")
            yearCounter = yearCounter + 1
        Next
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("<td>")
        Response.Write("<div id='error_leaseEndDateYear' class='edit_error'>&laquo; OBS!<br></div>")
        Response.Write("<div id='error_leaseEndDateMonth' class='edit_error'>&laquo; OBS!<br></div>")
        Response.Write("<div id='error_leaseEndDateDay' class='edit_error'>&laquo; OBS!<br></div>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Action:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='actionID' id='actionID'>")
        Response.Write("<option value=''> Select action  ")
        'Response.Write "<option value='1'> Just deactivate  "
        Response.Write("<option value='2'> Deactivate and create invoices  ")
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("<td>")
        Response.Write("<div id='error_actionID' class='edit_error'>&laquo; OBS!<br></div>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr>")
        Response.Write("<td align='right' colspan='3'>")
        Response.Write("<input type='submit' value='Continue'>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("</form>")
        Response.Write("<tr><td colspan='3'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("<td width='100'></td>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        Response.Write("<tr><td class='headline' colspan='4'>LEASE INFORMATION<hr class='hrHeadline'></td></tr>")
        Response.Write("<tr bgcolor='#eeeeee'><td>Country:</td><td>" & country & "</td></tr>")
        Response.Write("<tr><td>Partner:</td><td>" & partnerName & "</td></tr>")
        Response.Write("<tr bgcolor='#eeeeee'><td>Client:</td><td>" & client & "</td></tr>")
        Response.Write("<tr><td>Product:</td><td>" & product & "</td></tr>")
        Response.Write("<tr bgcolor='#eeeeee'><td>Duration:</td><td>" & vehicleCount & "/" & priceListDuration & " months</td></tr>")
        Response.Write("<tr><td>Lease contract start:</td><td>" & leaseStartDate & "</td></tr>")
        Response.Write("<tr bgcolor='#eeeeee'><td>Lease contract end:</td><td>" & leaseEndDate & "</td></tr>")
        Response.Write("<tr><td>Driving distance:</td><td>" & priceListDistance & " km</td></tr>")
        Response.Write("<tr bgcolor='#eeeeee'><td>Price category:</td><td>" & priceListCategory & " - " & price & " " & countryCurrency & "</td></tr>")
        Response.Write("<tr><td colspan='2'>The price includes the cost of the additional products</td></tr>")
        Response.Write("<tr><td colspan='4'><hr class='hrHeadline'></td></tr>")
        dbOpen()
        RecSet = Conn.Execute("Select Count(claimID) As claimCount From tblClaim Where vehicleID = " & vehicleID & " And paymentDate Is Null")
        claimCount = RecSet.Fields("claimCount").Value
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        If CDbl(claimCount) > 0 Then
            Response.Write("<tr><td colspan='2'><br></td></tr>")
            Response.Write("<tr><td class='headline' colspan='2'>INFORMATION<hr class='hrHeadline'></td></tr>")
            Response.Write("<tr bgcolor='#eeeeee'><td colspan='2'><font color='red'>OBS!</font> This vehicle has " & claimCount & " unpaid claim(s).</td></tr>")
            Response.Write("<tr><td colspan='2'><hr class='hrHeadline'></td></tr>")
        End If
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("</table>")
        Under()
    ElseIf Request.QueryString("do") = "action" Then
        leaseEndKms = Request.Form("leaseEndKms")
        leaseEndDateYear = Request.Form("leaseEndDateYear")
        leaseEndDateMonth = Request.Form("leaseEndDateMonth")
        leaseEndDateDay = Request.Form("leaseEndDateDay")
        actionID = Request.Form("actionID")
        leaseEndKms = CLng(leaseEndKms)
        leaseEndDateYear = CLng(leaseEndDateYear)
        leaseEndDateMonth = CLng(leaseEndDateMonth)
        leaseEndDateDay = CLng(leaseEndDateDay)
        actionID = CLng(actionID)
        If CLng(leaseEndDateMonth) < 10 Then
            leaseEndDateMonth = "0" & leaseEndDateMonth & ""
        End If
        If CLng(leaseEndDateDay) < 10 Then
            leaseEndDateDay = "0" & leaseEndDateDay & ""
        End If
        leaseEndDate = leaseEndDateYear & "-" & leaseEndDateMonth & "-" & leaseEndDateDay
        If IsDate(leaseEndDate) = False Then
            Response.Redirect("addInvoiceCredit.aspx?vehicleID=" & vehicleID & "&errorType=1&subDo=%20-%20NOT%20A%20VALID%20DATE")
        End If
        If Len(leaseEndKms) = 0 Then
            leaseEndKms = 0
        Else
            leaseEndKms = CLng(leaseEndKms)
        End If
        If CInt(actionID) = 1 Then
            Response.Redirect("addInvoiceCredit.aspx?do=justDeactivate&vehicleID=" & vehicleID & "&leaseEndDate=" & leaseEndDate & "&leaseEndKms=" & leaseEndKms & "")
        ElseIf CInt(actionID) = 2 Then
            Response.Redirect("addInvoiceCredit.aspx?do=save&vehicleID=" & vehicleID & "&leaseEndDate=" & leaseEndDate & "&leaseEndKms=" & leaseEndKms & "")
        End If
    ElseIf Request.QueryString("do") = "save" Then
        leaseEndKms = Request.QueryString("leaseEndKms")
        leaseEndDate = Request.QueryString("leaseEndDate")
        actLeaseEndDate = leaseEndDate
        realTime = Now()
        realTime = Left(realTime, 10)
        dbOpen()
        RecSet = Conn.Execute("Select v.leaseStartDate, v.partnerID, v.countryID, p.allowInvoiceDistancePenalty, p.allowInvoiceDurationPenalty, p.allowInvoiceServiceAccountBalance, pl.priceListID, pl.priceListDuration, pl.priceListDistance, pl.price, pl.uw, pl.pricePerKmOverdue, pl.uwPerKmOverdue, c.countryCurrency, c.decimals From (((tblVehicle v INNER JOIN tblProduct p ON p.productID = v.productID) INNER JOIN tblPriceList pl ON pl.priceListID = v.priceListID) INNER JOIN tblCountry c ON c.countryID = v.countryID) Where v.vehicleID = " & vehicleID & "")
        If Not RecSet.EOF Then
            leaseStartDate = RecSet.Fields("leaseStartDate").Value
            partnerID = RecSet.Fields("partnerID").Value
            countryID = RecSet.Fields("countryID").Value
            allowInvoiceDistancePenalty = RecSet.Fields("allowInvoiceDistancePenalty").Value
            allowInvoiceDurationPenalty = RecSet.Fields("allowInvoiceDurationPenalty").Value
            allowInvoiceServiceAccountBalance = RecSet.Fields("allowInvoiceServiceAccountBalance").Value
            priceListID = RecSet.Fields("priceListID").Value
            priceListDuration = RecSet.Fields("priceListDuration").Value
            priceListDistance = RecSet.Fields("priceListDistance").Value
            price = RecSet.Fields("price").Value
            uw = RecSet.Fields("uw").Value
            pricePerKmOverdue = RecSet.Fields("pricePerKmOverdue").Value
            uwPerKmOverdue = RecSet.Fields("uwPerKmOverdue").Value
            countryCurrency = RecSet.Fields("countryCurrency").Value
            decimals = RecSet.Fields("decimals").Value
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        dbOpen()
        RecSet = Conn.Execute("Select Sum(ap.price) As additionalPrice, Sum(ap.uw) As additionalUw From (tblAdditionalProductVehicleContent apc INNER JOIN tblAdditionalProduct ap ON ap.additionalProductID = apc.additionalProductID) Where apc.vehicleID = " & vehicleID & "")
        If Not RecSet.EOF Then
            additionalPrice = RecSet.Fields("additionalPrice").Value
            additionalUw = RecSet.Fields("additionalUw").Value
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        If IsDBNull(additionalPrice) = True Then
            additionalPrice = 0
        End If
        If IsDBNull(additionalUw) = True Then
            additionalUw = 0
        End If
        price = price + additionalPrice
        uw = uw + additionalUw

        If DateDiff("d", Convert.ToDateTime(leaseStartDate), Convert.ToDateTime(leaseEndDate)) < 0 Then
            Response.Redirect("addInvoiceCredit.aspx?vehicleID=" & vehicleID & "&errorType=2&subDo=%20-%20NOT%20A%20VALID%20DATE")
        End If
        dbOpen()
        RecSet = Conn.Execute("Select Top 1 leaseCurrentDate As firstLeaseDate From tblInvoiceContent Where vehicleID = " & vehicleID & " Order By leaseCurrentDate")
        If RecSet.EOF Then
            firstLeaseDate = ""
        Else
            firstLeaseDate = RecSet.Fields("firstLeaseDate").Value
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        If Len(firstLeaseDate) = 0 Then
            Response.Redirect("addInvoiceCredit.aspx?vehicleID=" & vehicleID & "&subDo=%20-%20NOT%20INVOICED%20YET%20")
        End If
        dbOpen()
        RecSet = Conn.Execute("Select Top 1 leaseCurrentDate As lastLeaseDate From tblInvoiceContent Where vehicleID = " & vehicleID & " Order By leaseCurrentDate desc")
        If RecSet.EOF Then
            lastLeaseDate = ""
        Else
            lastLeaseDate = RecSet.Fields("lastLeaseDate").Value
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        orgLeaseEndDate = DateAdd("m", CDbl(priceListDuration), Convert.ToDateTime(leaseStartDate))
        orgLeaseEndDate = DateAdd("m", CDbl("1"), Convert.ToDateTime(orgLeaseEndDate))
        orgLeaseEndDate = Left(orgLeaseEndDate, 8) & "01"
        orgLeaseEndDate = DateAdd("d", CDbl("-1"), Convert.ToDateTime(orgLeaseEndDate))
        leaseEndDate = DateAdd("m", CDbl("1"), Convert.ToDateTime(leaseEndDate))
        leaseEndDate = Left(leaseEndDate, 8) & "01"
        leaseEndDate = DateAdd("d", CDbl("-1"), Convert.ToDateTime(leaseEndDate))
        invoiceDate = realTime
        paymentTerm = 10
        expiryDate = DateAdd("d", paymentTerm, Convert.ToDateTime(invoiceDate))
        Response.Write("realTime = " & realTime & "<br>")
        Response.Write("LeaseEndKms = " & leaseEndKms & "<br>")
        Response.Write("leaseStartDate = " & leaseStartDate & "<br>")
        Response.Write("firstLeaseDate = " & firstLeaseDate & "<br>")
        Response.Write("lastLeaseDate = " & lastLeaseDate & "<br>")
        Response.Write("orgLeaseEndDate = " & orgLeaseEndDate & "<br>")
        Response.Write("actLeaseEndDate = " & actLeaseEndDate & "<br>")
        Response.Write("leaseEndDate = " & leaseEndDate & "<br>")
        Response.Write("allowInvoiceServiceAccountBalance = " & allowInvoiceServiceAccountBalance & "<br>")
        If lastLeaseDate > leaseEndDate Then
            dbOpen()
            RecSet = Conn.Execute("Select invoiceCreditID From tblInvoiceCredit Where partnerID = " & partnerID & " And userID IS NULL")
            If RecSet.EOF Then
                invoiceCreditID = 0
            Else
                invoiceCreditID = RecSet.Fields("invoiceCreditID").Value
            End If
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            If CInt(invoiceCreditID) = 0 Then
                dbOpen()
                RecSet = Conn.Execute("Select invoiceID From tblInvoice Where partnerID = " & partnerID & " And userID IS NULL")
                invoiceID = RecSet.Fields("invoiceID").Value
                RecSet.Close()
                RecSet = Nothing
                dbClose()
                dbOpen()
                Response.Write("Insert Into tblInvoiceCredit (invoiceID, countryID, partnerID) Values(" & invoiceID & "," & countryID & "," & partnerID & ")<br><br>")
                Conn.Execute("Insert Into tblInvoiceCredit (invoiceID, countryID, partnerID) Values(" & invoiceID & "," & countryID & "," & partnerID & ")")
                dbClose()
                dbOpen()
                RecSet = Conn.Execute("SELECT MAX(invoiceCreditID) AS invoiceCreditID FROM tblInvoiceCredit")
                invoiceCreditID = RecSet.Fields("invoiceCreditID").Value
                RecSet.Close()
                RecSet = Nothing
                dbClose()
                Response.Write("invoiceCreditID = " & invoiceCreditID & "<br><br>")
            End If
            dbOpen()
            RecSet = Conn.Execute("Select ic.invoiceID, ic.leaseCurrentDate From (tblInvoiceContent ic INNER JOIN tblInvoice i ON i.invoiceID = ic.invoiceID) Where ic.vehicleID = " & vehicleID & " And ic.leaseCurrentDate > '" & leaseEndDate & "'")
            If Not RecSet.EOF Then
                Do Until RecSet.EOF
                    invoiceID = RecSet.Fields("invoiceID").Value
                    leaseCurrentDate = RecSet.Fields("leaseCurrentDate").Value
                    Response.Write("Insert Into tblInvoiceCreditContent (invoiceCreditID, invoiceID, vehicleID, priceListID, leaseCurrentDate) Values(" & invoiceCreditID & "," & invoiceID & "," & vehicleID & "," & priceListID & ",'" & leaseCurrentDate & "')<br><br>")
                    Conn.Execute("Insert Into tblInvoiceCreditContent (invoiceCreditID, invoiceID, vehicleID, priceListID, leaseCurrentDate) Values(" & invoiceCreditID & "," & invoiceID & "," & vehicleID & "," & priceListID & ",'" & leaseCurrentDate & "')")
                    RecSet.MoveNext()
                Loop
            End If
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            dbOpen()
            RecSet = Conn.Execute("Select ica.invoiceID, ica.additionalProductID, ica.leaseCurrentDate From (tblInvoiceContentAdditional ica INNER JOIN tblInvoice i ON i.invoiceID = ica.invoiceID) Where ica.vehicleID = " & vehicleID & " And ica.leaseCurrentDate > '" & leaseEndDate & "'")
            If Not RecSet.EOF Then
                Do Until RecSet.EOF
                    invoiceID = RecSet.Fields("invoiceID").Value
                    additionalProductID = RecSet.Fields("additionalProductID").Value
                    leaseCurrentDate = RecSet.Fields("leaseCurrentDate").Value
                    Response.Write("Insert Into tblInvoiceCreditContentAdditional (invoiceCreditID, invoiceID, vehicleID, additionalProductID, leaseCurrentDate) Values(" & invoiceCreditID & "," & invoiceID & "," & vehicleID & "," & additionalProductID & ",'" & leaseCurrentDate & "')<br><br>")
                    Conn.Execute("Insert Into tblInvoiceCreditContentAdditional (invoiceCreditID, invoiceID, vehicleID, additionalProductID, leaseCurrentDate) Values(" & invoiceCreditID & "," & invoiceID & "," & vehicleID & "," & additionalProductID & ",'" & leaseCurrentDate & "')")
                    RecSet.MoveNext()
                Loop
            End If
            RecSet.Close()
            RecSet = Nothing
            dbClose()
        End If
        dbOpen()
        RecSet = Conn.Execute("Select Count(invoiceID) As invoiceCount From tblInvoiceContent Where vehicleID = " & vehicleID & "")
        If RecSet.EOF Then
            invoiceCount = 0
        Else
            invoiceCount = RecSet.Fields("invoiceCount").Value
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("invoiceCount = " & invoiceCount & "<br>")
        dbOpen()
        RecSet = Conn.Execute("Select Count(invoiceCreditID) As creditCount From tblInvoiceCreditContent Where vehicleID = " & vehicleID & "")
        If RecSet.EOF Then
            creditCount = 0
        Else
            creditCount = RecSet.Fields("creditCount").Value
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        invoiceCount = CDbl(invoiceCount) - CDbl(creditCount)
        monthsDiff = CDbl(priceListDuration) - CDbl(invoiceCount)
        Response.Write("creditCount = " & creditCount & "<br>")
        Response.Write("invoiceCount = " & invoiceCount & "<br>")
        Response.Write("monthsDiff = " & monthsDiff & "<br><br>")
        If allowInvoiceDurationPenalty = 1 And monthsDiff >= 3 Then
            invoiceAmount = CDbl(price) * 3
            invoiceAmount = Replace(CStr(invoiceAmount), ",", ".")
            invoiceClaim = invoiceAmount
            uw = CDbl(uw) * 3
            uw = Replace(CStr(uw), ",", ".")
            invoiceText = "Penalty fee due to early lease contract cancellation on " & leaseEndDate & "."
            dbOpen()
            Response.Write("Insert Into tblInvoiceDurationPenalty (vehicleID, invoiceAmount, invoiceClaim, uw, invoiceDate, expiryDate, paymentTerm, invoiceText) Values(" & vehicleID & "," & invoiceAmount & "," & invoiceClaim & "," & uw & ",'" & invoiceDate & "','" & expiryDate & "'," & paymentTerm & ",'" & invoiceText & "')<br><br>")
            Conn.Execute("Insert Into tblInvoiceDurationPenalty (vehicleID, invoiceAmount, invoiceClaim, uw, invoiceDate, expiryDate, paymentTerm, invoiceText) Values(" & vehicleID & "," & invoiceAmount & "," & invoiceClaim & "," & uw & ",'" & invoiceDate & "','" & expiryDate & "'," & paymentTerm & ",'" & invoiceText & "')")
            dbClose()
        End If
        If allowInvoiceDistancePenalty = 1 And CInt(leaseEndKms) > 0 Then
            allowedKmsPerMonth = CDbl(priceListDistance) / CDbl(priceListDuration)
            allowedKms = allowedKmsPerMonth * CDbl(invoiceCount)
            Response.Write("allowedKmsPerMonth = " & priceListDistance & " / " & priceListDuration & " = " & allowedKmsPerMonth & "<br>")
            Response.Write("allowedKms = " & allowedKmsPerMonth & " * " & invoiceCount & " = " & allowedKms & "<br>")
            kmDiff = CDbl(leaseEndKms) - allowedKms
            If kmDiff >= 250 Then
                invoiceAmount = kmDiff * CDbl(pricePerKmOverdue)
                Response.Write("invoiceAmount = (" & leaseEndKms & " - " & allowedKms & ") * " & pricePerKmOverdue & " = " & invoiceAmount & "<br>")
                If CInt(invoiceAmount) > 0 Then
                    invoiceAmount = Replace(CStr(invoiceAmount), ",", ".")
                    invoiceClaim = invoiceAmount
                    uw = kmDiff * CDbl(uwPerKmOverdue)
                    uw = Replace(CStr(uw), ",", ".")
                    invoiceText = "Penalty fee due to kilometer stand overdue of " & FormatNumber(kmDiff, 2) & " km on contract cancellation. The fee is " & pricePerKmOverdue & " " & countryCurrency & " / km."
                    dbOpen()
                    Response.Write("Insert Into tblInvoiceDistancePenalty (vehicleID, invoiceAmount, invoiceClaim, uw, invoiceDate, expiryDate, paymentTerm, invoiceText) Values(" & vehicleID & "," & invoiceAmount & "," & invoiceClaim & "," & uw & ",'" & invoiceDate & "','" & expiryDate & "'," & paymentTerm & ",'" & invoiceText & "')<br><br>")
                    Conn.Execute("Insert Into tblInvoiceDistancePenalty (vehicleID, invoiceAmount, invoiceClaim, uw, invoiceDate, expiryDate, paymentTerm, invoiceText) Values(" & vehicleID & "," & invoiceAmount & "," & invoiceClaim & "," & uw & ",'" & invoiceDate & "','" & expiryDate & "'," & paymentTerm & ",'" & invoiceText & "')")
                    dbClose()
                End If
            End If
        End If
        'remove leaseEndDate < realTime
        If allowInvoiceServiceAccountBalance = 1  Then
            dbOpen()
            RecSet = Conn.Execute("Select Sum(paymentAmount) As claimSum From tblClaim Where vehicleID = " & vehicleID & "")
            claimSum = RecSet.Fields("claimSum").Value
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            If IsDBNull(claimSum) = True Then
                claimSum = 0
            End If
            Response.Write("claimSum = " & claimSum & "<br>")
            invoiceAmount = CDbl(price) * CDbl(invoiceCount)
            invoiceAmount = CDbl(claimSum) - CDbl(invoiceAmount)
            invoiceAmount = CDbl(invoiceAmount)
            invoiceAmount = Replace(CStr(invoiceAmount), ",", ".")
            invoiceClaim = invoiceAmount
            uw = 0
            invoiceText = "Invoice amount due to service account balance. This invoice is based on a lease period of " & invoiceCount & " month(s) with a monthly cost of " & FormatNumber(CDbl(price), decimals) & " " & countryCurrency & ", and an total service cost of " & FormatNumber(CDbl(claimSum), decimals) & " " & countryCurrency & "."
            dbOpen()
            Response.Write("Insert Into tblInvoiceServiceAccountBalance (vehicleID, invoiceAmount, invoiceClaim, uw, invoiceDate, expiryDate, paymentTerm, invoiceText) Values(" & vehicleID & "," & invoiceAmount & "," & invoiceClaim & "," & uw & ",'" & invoiceDate & "','" & expiryDate & "'," & paymentTerm & ",'" & invoiceText & "')<br><br>")
            Conn.Execute("Insert Into tblInvoiceServiceAccountBalance (vehicleID, invoiceAmount, invoiceClaim, uw, invoiceDate, expiryDate, paymentTerm, invoiceText) Values(" & vehicleID & "," & invoiceAmount & "," & invoiceClaim & "," & uw & ",'" & invoiceDate & "','" & expiryDate & "'," & paymentTerm & ",'" & invoiceText & "')")
            dbClose()
        End If
        If actLeaseEndDate < realTime Then
            dbOpen()
            Response.Write("Update tblVehicle Set active = 0 Where vehicleID = " & vehicleID & "<br><br>")
            Conn.Execute("Update tblVehicle Set active = 0 Where vehicleID = " & vehicleID & "")
            dbClose()
        End If
        dbOpen()
        Response.Write("Update tblVehicle Set leaseEndDate = '" & actLeaseEndDate & "', leaseEndKms = " & leaseEndKms & ", consumedDistance = " & leaseEndKms & " Where vehicleID = " & vehicleID & "<br>")
        Conn.Execute("Update tblVehicle Set leaseEndDate = '" & actLeaseEndDate & "', leaseEndKms = " & leaseEndKms & ", consumedDistance = " & leaseEndKms & " Where vehicleID = " & vehicleID & "")
        dbClose()
        Response.Redirect("editVehicle.aspx?vehicleID=" & vehicleID & "&subDo=%20-%20DEACTIVATED%20AND%20INVOICE%20CREATED")
    ElseIf Request.QueryString("do") = "justDeactivate" Then
        leaseEndKms = Request.QueryString("leaseEndKms")
        leaseEndDate = Request.QueryString("leaseEndDate")
        leaseEndKms = CLng(leaseEndKms)
        dbOpen()
        RecSet = Conn.Execute("Select ic.invoiceID From (tblInvoiceContent AS ic INNER JOIN tblInvoice AS i ON i.invoiceID = ic.invoiceID) WHERE ic.vehicleID = " & vehicleID & " AND i.invoiceAmount = 0")
        If Not RecSet.EOF Then
            invoiceID = RecSet.Fields("invoiceID").Value
            Response.Write("Delete From tblInvoiceContent Where vehicleID = " & vehicleID & " And invoiceID = " & invoiceID & "<br>")
            Conn.Execute("Delete From tblInvoiceContent Where vehicleID = " & vehicleID & " And invoiceID = " & invoiceID & "")
        End If
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        dbOpen()
        Response.Write("Update tblVehicle Set leaseEndDate = '" & leaseEndDate & "', leaseEndKms = " & leaseEndKms & ", consumedDistance = " & leaseEndKms & ", active = 0 Where vehicleID = " & vehicleID & "<br>")
        Conn.Execute("Update tblVehicle Set leaseEndDate = '" & leaseEndDate & "', leaseEndKms = " & leaseEndKms & ", consumedDistance = " & leaseEndKms & ", active = 0 Where vehicleID = " & vehicleID & "")
        dbClose()
        Response.Redirect("editVehicle.aspx?vehicleID=" & vehicleID & "&subDo=%20-%20DEACTIVATED")
    End If
%>
